/*
 *  Copyright 2008-2010 www.jrails.org
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */

package org.rails.core.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.rails.core.model.ActiveRecord;
import org.rails.core.model.attribute.AttributeException;
import org.rails.core.sql.Relation;
import org.rails.core.sql.SQLDeleteBuilder;
import org.rails.core.sql.SQLFactory;
import org.rails.core.sql.SQLInsertBuilder;
import org.rails.core.sql.SQLManager;
import org.rails.core.sql.SQLQueryBuilder;
import org.rails.core.sql.SQLUpdateBuilder;
import org.rails.core.util.Utils;

/**
 * 事务管理器，执行SQL并提供事务支持。
 * 
 * @author Jack Ye
 * 
 */
public final class SQLTransaction {

	private final Logger logger = Logger.getLogger(this.getClass());
	private DataSource dataSource;
	private Connection connection;
	private SQLManager sqlManager;

	/**
	 * 根据提供的数据源构造新的事务管理器。
	 * 
	 * @param dataSource
	 *            数据源。
	 * @throws SQLException
	 */
	public SQLTransaction(DataSource dataSource) throws SQLException {
		super();
		this.dataSource = dataSource;
		open();
	}

	/**
	 * 执行查询，根据SQLManager创建查询SQL。
	 * 
	 * @param sqlManager
	 * @return 查询结果列表。
	 * @throws SQLException
	 */
	public List<Map<String, Object>> findBySqlManager(SQLManager sqlManager)
			throws SQLException {
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLQueryBuilder sqb = sf.getQueryBuilder();
		logger.debug("Execute SQL : " + sqb.getSql());
		logger.debug("SQL Parameter : " + sqb.getParams());
		final StatementHelper statementHelper = new StatementHelper(connection,
				sqb.getSql(), sqb.getParams());
		statementHelper.setMaxRows(sqlManager.getMaxResult());
		try {
			return statementHelper.executeQuery();
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	/**
	 * 执行查询，根据SQLManager创建查询SQL。
	 * 
	 * @param <T>
	 *            ActiveRecord
	 * @param record
	 *            ActiveRecord对象
	 * @param sqlManager
	 * @return ActiveRecord列表集合。
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public <T extends ActiveRecord> List<T> findBySqlManager(T record,
			SQLManager sqlManager) throws SQLException {
		final List<T> res = new ArrayList<T>();
		final List<Map<String, Object>> list = findBySqlManager(sqlManager);
		for (Iterator<Map<String, Object>> iterator = list.iterator(); iterator
				.hasNext();) {
			final T t = (T) record.clone();
			t.putAll(iterator.next());
			res.add(t);
		}
		return res;
	}

	/**
	 * 
	 * @param sqlManager
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> findFirst(SQLManager sqlManager)
			throws SQLException {
		sqlManager.setMaxResult(1);
		final List<Map<String, Object>> ls = findBySqlManager(sqlManager);
		if (ls == null || ls.size() == 0)
			return null;
		else
			return ls.get(0);
	}

	public <T extends ActiveRecord> T findFirst(T record, SQLManager sqlManager)
			throws SQLException {
		sqlManager.setMaxResult(1);
		final List<Map<String, Object>> ls = findBySqlManager(sqlManager);
		if (ls == null || ls.size() == 0)
			return null;
		else {
			record.putAll(ls.get(0));
			return record;
		}
	}

	public List<Map<String, Object>> findBySql(String sql, List<Object> params)
			throws SQLException {
		logger.debug("Execute SQL : " + sql);
		logger.debug("SQL Parameter : " + params);
		final StatementHelper statementHelper = new StatementHelper(connection,
				sql, params);
		try {
			return statementHelper.executeQuery();
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public List<Map<String, Object>> findBySql(String sql, Object[] params)
			throws SQLException {
		return findBySql(sql, Arrays.asList(params));
	}

	@SuppressWarnings("unchecked")
	public boolean create(ActiveRecord record) throws SQLException,
			AttributeException {
		final Timestamp curr = new Timestamp(new Date().getTime());
		record.put("created_at", curr);
		record.put("updated_at", curr);
		record.clearOtherAttribute();
		record.removeId();

		final List<String> atts = record.getAttributes();
		atts.remove(record.getTable().getPrimaryKey());

		final List<String> currAtts = Utils.getKeys(record);
		currAtts.remove(record.getTable().getPrimaryKey());

		final List<String> notCurrAtts = new ArrayList<String>();
		for (String att : atts) {
			if (!currAtts.contains(att))
				notCurrAtts.add(att);
		}
		record.validateAttribute(currAtts, notCurrAtts);

		sqlManager = new SQLManager(record);
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLInsertBuilder sib = sf.getInsertBuilder();
		logger.debug("Execute SQL : " + sib.getSql());
		logger.debug("SQL Parameter : " + sib.getParams());
		final StatementHelper statementHelper = new StatementHelper(connection,
				sib.getSql(), sib.getParams());
		try {
			return statementHelper.executeCreate(record) > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean update(ActiveRecord record) throws AttributeException,
			SQLException {
		final Timestamp curr = new Timestamp(new Date().getTime());
		record.put("updated_at", curr);
		record.removeAttribute("created_at");
		record.clearOtherAttribute();
		record.validateAttribute(Utils.getKeys(record), null);
		sqlManager = new SQLManager(record);
		sqlManager.addCondition(Utils.format("{0} = :{1}", record.getTable()
				.getFormatPrimaryKey(), record.getTable().getPrimaryKey()));
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLUpdateBuilder sib = sf.getUpdateBuilder();
		logger.debug("Execute SQL : " + sib.getSql());
		logger.debug("SQL Parameter : " + sib.getParams());
		final StatementHelper statementHelper = new StatementHelper(connection,
				sib.getSql(), sib.getParams());
		try {
			return statementHelper.executeUpdate() > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public boolean delete(ActiveRecord record) throws SQLException {
		sqlManager = new SQLManager(record);
		sqlManager.addCondition(Utils.format("{0} = :{1}", record.getTable()
				.getFormatPrimaryKey(), record.getTable().getPrimaryKey()));
		sqlManager.addParameter(record.getTable().getPrimaryKey(),
				record.getId());
		final SQLFactory sf = new SQLFactory(sqlManager);
		final SQLDeleteBuilder sdb = sf.getDeleteBuilder();
		logger.debug("Execute SQL : " + sdb.getSql());
		logger.debug("SQL Parameter : " + sdb.getParams());
		final StatementHelper statementHelper = new StatementHelper(connection,
				sdb.getSql(), sdb.getParams());
		try {
			return statementHelper.executeUpdate() > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public <T extends ActiveRecord> int delete(T record, String condition,
			Object... params) throws SQLException {
		final String sql = Utils.format("DELETE FROM {0}{1}{2}", record
				.getTable().getFormatName(), Utils.isEmpty(condition) ? ""
				: " WHERE ", condition);
		final StatementHelper statementHelper = new StatementHelper(connection,
				sql, Arrays.asList(params));
		try {
			return statementHelper.executeUpdate();
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public int executeUpdate(String sql, List<Object> params)
			throws SQLException {
		logger.debug("Execute SQL : " + sql);
		logger.debug("SQL Parameter : " + params);
		final StatementHelper statementHelper = new StatementHelper(connection,
				sql, params);
		try {
			return statementHelper.executeUpdate();
		} finally {
			statementHelper.close();
		}
	}

	public int executeUpdate(String sql, Object[] params) throws SQLException {
		return executeUpdate(sql, Arrays.asList(params));
	}

	public int[] createBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		final StatementHelper statementHelper = new StatementHelper(connection);
		try {
			return statementHelper.executeInsertBatch(records);
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public int[] deleteBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		final StatementHelper statementHelper = new StatementHelper(connection);
		try {
			return statementHelper.executeDeleteBatch(records);
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public int[] updateBatch(List<? extends ActiveRecord> records)
			throws SQLException {
		final StatementHelper statementHelper = new StatementHelper(connection);
		try {
			return statementHelper.executeUpdateBatch(records);
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public <T extends ActiveRecord> List<T> marries(ActiveRecord source,
			T target) throws SQLException {
		return marries(source, target, target.getClass().getSimpleName(),
				new HashMap<String, Object>());
	}

	public <T extends ActiveRecord> List<T> marries(ActiveRecord source,
			T target, Map<String, Object> params) throws SQLException {
		return marries(source, target, target.getClass().getSimpleName(),
				params);
	}

	public <T extends ActiveRecord> List<T> marries(ActiveRecord source,
			T target, final String asName) throws SQLException {
		return marries(source, target, asName, new HashMap<String, Object>());
	}

	@SuppressWarnings("unchecked")
	public <T extends ActiveRecord> List<T> marries(ActiveRecord source,
			T target, String asName, Map<String, Object> params)
			throws SQLException {
		final Relation relation = new Relation(source, target, asName, params);
		final List<T> many = new ArrayList<T>();
		final List<Map<String, Object>> list = findBySqlManager(relation
				.getMarriesSqlManager());
		for (Iterator<Map<String, Object>> iterator = list.iterator(); iterator
				.hasNext();) {
			final Map<String, Object> map = (Map<String, Object>) iterator
					.next();
			final ActiveRecord record = (ActiveRecord) target.clone();
			record.clear();
			record.putAll(map);
			many.add((T) record);
		}
		return many;
	}

	public boolean married(ActiveRecord source, ActiveRecord target)
			throws SQLException {
		final Relation relation = new Relation(source, target);
		final Object[] params = new Object[] { source.getId(), target.getId() };
		final StatementHelper statementHelper = new StatementHelper(connection,
				relation.getMarriedSql(), Arrays.asList(params));
		try {
			return ((Long) statementHelper.executeQuery().get(0).get("c")) > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public boolean marry(ActiveRecord source, ActiveRecord target)
			throws SQLException {
		final Relation relation = new Relation(source, target);
		final StatementHelper statementHelper = new StatementHelper(connection,
				relation.getMarrySql(), Arrays.asList(new Object[] {
						source.getId(), target.getId() }));
		try {
			return statementHelper.executeUpdate() > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public boolean unMarry(ActiveRecord source, ActiveRecord target)
			throws SQLException {
		final Relation relation = new Relation(source, target);
		final StatementHelper statementHelper = new StatementHelper(connection,
				relation.getUnMarrySql(), Arrays.asList(new Object[] {
						source.getId(), target.getId() }));
		try {
			return statementHelper.executeUpdate() > 0;
		} finally {
			if (statementHelper != null)
				statementHelper.close();
		}
	}

	public List<Map<String, Object>> executeProcResult(String procName,
			Object[] procParams, int... outIndex) throws SQLException {
		final List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
		Map<String, Object> row = null;
		ResultSetMetaData rsmd = null;
		ResultSet result = null;
		CallableStatement cstm = null;
		try {
			cstm = connection.prepareCall("{CALL " + procName + "}");
			if (procParams != null) {
				for (int i = 0; i < procParams.length; i++) {
					cstm.setObject(i + 1, procParams[i]);
				}
			}
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					cstm.registerOutParameter(outIndex[i] + 1,
							Types.JAVA_OBJECT);
				}
			}
			result = cstm.executeQuery();
			rsmd = result.getMetaData();
			int c = rsmd.getColumnCount();
			while (result.next()) {
				row = new HashMap<String, Object>();
				for (int i = 0; i < c; i++) {
					row.put(rsmd.getColumnName(i + 1).toLowerCase(),
							result.getObject(i + 1));
				}
				rows.add(row);
			}
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					procParams[outIndex[i]] = cstm.getObject(outIndex[i] + 1);
				}
			}
			return rows;
		} finally {
			if (result != null)
				result.close();
			if (cstm != null)
				cstm.close();
		}
	}

	public void executeProc(String procName, Object[] procParams,
			int... outIndex) throws SQLException {
		CallableStatement cstm = null;
		try {
			cstm = connection.prepareCall("{CALL " + procName + "}");
			if (procParams != null) {
				for (int i = 0; i < procParams.length; i++) {
					cstm.setObject(i + 1, procParams[i]);
				}
			}
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					cstm.registerOutParameter(outIndex[i] + 1, Types.VARCHAR);
				}
			}
			cstm.execute();
			if (outIndex != null) {
				for (int i = 0; i < outIndex.length; i++) {
					procParams[outIndex[i]] = cstm.getObject(outIndex[i] + 1);
				}
			}
		} finally {
			if (cstm != null)
				cstm.close();
		}
	}

	private void open() throws SQLException {
		logger.info("Open Connection");
		logger.info("Set connection auto commit is false");
		connection = getDataSource().getConnection();
		connection.setAutoCommit(false);
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public Connection getConnection() {
		return connection;
	}

	private void close() {
		logger.info("Close Connection");
		try {
			if (connection != null)
				connection.close();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		}
	}

	public boolean rollback() {
		logger.info("Rollback Connection");
		logger.info("Set connection auto commit is true");
		try {
			if (connection != null) {
				connection.rollback();
				connection.setAutoCommit(true);
			}
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			return false;
		} finally {
			close();
		}
	}

	public boolean commit() {
		logger.info("Commit Connection");
		logger.info("Set connection auto commit is true");
		try {
			if (connection != null) {
				connection.commit();
				connection.setAutoCommit(true);
			}
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			return false;
		} finally {
			close();
		}
	}

}
